﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Reflection;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace M
{
    public class MainDB : IDisposable
    {
        private IDbConnection _connection;
        private IDbTransaction _transaction;
        public String conn = String.Empty;

        /// <summary>
        /// Initializes a new instance of the <see cref="MainDB_Base"/> 
        /// class and opens the database connection.
        /// </summary>
        public MainDB()
            : this(true)
        {
            // EMPTY
        }

        public DataTable SelectQuery(string sql)
        {
            IDbCommand cmd = this.CreateCommand(sql, false);
            return this.CreateDataTable(cmd);
        }
        public DataTable SelectQuery(string sql, string[] parNames, object[] parValues)
        {
            try
            {
                SqlParameter sqlPara;
                SqlCommand sqlCmd = (SqlCommand)this.CreateCommand(sql, false);

                int n = parNames.Length;
                for (int i = 0; i < n; i++)
                {
                    sqlPara = new SqlParameter();
                    sqlPara.ParameterName = (string)parNames.GetValue(i);
                    sqlPara.Value = parValues.GetValue(i);
                    sqlCmd.Parameters.Add(sqlPara);
                }
                return this.CreateDataTable(sqlCmd);
            }
            catch (Exception myException)
            {
                throw (new Exception(myException.Message));
            }
        }
        protected internal DataTable CreateDataTable(IDbCommand command)
        {
            DataTable dataTable = new DataTable();
            new System.Data.SqlClient.SqlDataAdapter((System.Data.SqlClient.SqlCommand)command).Fill(dataTable);
            return dataTable;
        }

        public DataTable CallStoredProcedure(System.Collections.Generic.List<SqlParameter> listOfPara, string nameOfStored, bool returnDataTable)
        {
            IDbCommand cmd = this.CreateCommand(nameOfStored, true);
            for (int i = 0; i < listOfPara.Count; i++)
            {
                SqlParameter sp = listOfPara[i];
                AddParameter(cmd, sp.ParameterName, sp.DbType, sp.Value);
            }
            if (returnDataTable)
            {
                return this.CreateDataTable(cmd);
            }
            else
            {
                cmd.ExecuteNonQuery();
                return null;
            }
        }
        public DataTable CallStoredProcedure(string spName, string[] parNames, object[] parValues)
        {
            try
            {
                SqlParameter sqlPara;
                SqlCommand sqlCmd = (SqlCommand)this.CreateCommand(spName, true);

                int n = parNames.Length;
                for (int i = 0; i < n; i++)
                {
                    sqlPara = new SqlParameter();
                    sqlPara.ParameterName = (string)parNames.GetValue(i);
                    sqlPara.Value = parValues.GetValue(i);
                    sqlCmd.Parameters.Add(sqlPara);
                }
                return this.CreateDataTable(sqlCmd);
            }
            catch (Exception myException)
            {
                throw (new Exception(myException.Message));
            }
        }
        public IDbCommand CallStoredProcedure(System.Collections.Generic.List<SqlParameter> listOfPara, string nameOfStored)
        {
            IDbCommand cmd = this.CreateCommand(nameOfStored, true);
            for (int i = 0; i < listOfPara.Count; i++)
            {
                SqlParameter sp = listOfPara[i];
                AddParameter(cmd, sp.ParameterName, sp.DbType, sp.Value, sp.Direction);
            }
            cmd.ExecuteNonQuery();
            return cmd;
        }
        public DataTable CallStoredProcedure(string nameOfStored, bool returnDataTable)
        {
            IDbCommand cmd = this.CreateCommand(nameOfStored, true);
            if (returnDataTable)
            {
                return this.CreateDataTable(cmd);
            }
            else
            {
                cmd.ExecuteNonQuery();
                return null;
            }
        }
        public void AnotherNonQuery(string sql)
        {
            IDbCommand cmd = this.CreateCommand(sql, false);
            cmd.ExecuteNonQuery();
        }
        /// <summary>
        /// Initializes a new instance of the <see cref="MainDB_Base"/> class.
        /// </summary>
        /// <param name="init">Specifies whether the constructor calls the
        /// <see cref="InitConnection"/> method to initialize the database connection.</param>
        protected MainDB(bool init)
        {
            if (init)
                InitConnection();
        }

        /// <summary>
        /// Initializes the database connection.
        /// </summary>
        protected void InitConnection()
        {
            _connection = CreateConnection();
            _connection.Open();
        }
        /// <summary>
        /// Creates a new connection to the database.
        /// </summary>
        /// <returns>A reference to the <see cref="System.Data.IDbConnection"/> object.</returns>
        protected IDbConnection CreateConnection()
        {
            return new System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
        }

        public object SelectScalar(string sql)
        {
            IDbCommand cmd = this.CreateCommand(sql, false);
            cmd.CommandText = sql;
            cmd.CommandType = CommandType.Text;
            return cmd.ExecuteScalar();
        }

        public object SelectScalar(string sql, string[] parNames, object[] parValues)
        {
            IDbCommand cmd = this.CreateCommand(sql, false);
            SqlParameter sqlPara;
            int n = parNames.Length;
            for (int i = 0; i < n; i++)
            {
                sqlPara = new SqlParameter();
                sqlPara.ParameterName = (string)parNames.GetValue(i);
                sqlPara.Value = parValues.GetValue(i);
                cmd.Parameters.Add(sqlPara);
            }
            cmd.CommandText = sql;
            cmd.CommandType = CommandType.Text;
            return cmd.ExecuteScalar();
        }

        /// <summary>
        /// Returns a SQL statement parameter name that is specific for the data provider.
        /// For example it returns ? for OleDb provider, or @paramName for MS SQL provider.
        /// </summary>
        /// <param name="paramName">The data provider neutral SQL parameter name.</param>
        /// <returns>The SQL statement parameter name.</returns>
        protected internal string CreateSqlParameterName(string paramName)
        {
            return "@" + paramName;
        }

        /// <summary>
        /// Creates <see cref="System.Data.IDataReader"/> for the specified DB command.
        /// </summary>
        /// <param name="command">The <see cref="System.Data.IDbCommand"/> object.</param>
        /// <returns>A reference to the <see cref="System.Data.IDataReader"/> object.</returns>
        protected internal virtual IDataReader ExecuteReader(IDbCommand command)
        {
            return command.ExecuteReader();
        }

        /// <summary>
        /// Adds a new parameter to the specified command. It is not recommended that 
        /// you use this method directly from your custom code. Instead use the 
        /// <c>AddParameter</c> method of the &lt;TableCodeName&gt;Collection_Base classes.
        /// </summary>
        /// <param name="cmd">The <see cref="System.Data.IDbCommand"/> object to add the parameter to.</param>
        /// <param name="paramName">The name of the parameter.</param>
        /// <param name="dbType">One of the <see cref="System.Data.DbType"/> values. </param>
        /// <param name="value">The value of the parameter.</param>
        /// <returns>A reference to the added parameter.</returns>
        internal IDbDataParameter AddParameter(IDbCommand cmd, string paramName,
                                                DbType dbType, object value)
        {
            IDbDataParameter parameter = cmd.CreateParameter();
            parameter.ParameterName = CreateCollectionParameterName(paramName);
            parameter.DbType = dbType;
            parameter.Value = null == value ? DBNull.Value : value;
            cmd.Parameters.Add(parameter);
            return parameter;
        }
        internal IDbDataParameter AddParameter(IDbCommand cmd, string paramName,
                                DbType dbType, object value, ParameterDirection direct)
        {
            IDbDataParameter parameter = cmd.CreateParameter();
            parameter.ParameterName = CreateCollectionParameterName(paramName);
            parameter.DbType = dbType;
            parameter.Value = null == value ? DBNull.Value : value;
            parameter.Direction = direct;
            cmd.Parameters.Add(parameter);
            return parameter;
        }
        /// <summary>
        /// Creates a .Net data provider specific name that is used by the 
        /// <see cref="AddParameter"/> method.
        /// </summary>
        /// <param name="baseParamName">The base name of the parameter.</param>
        /// <returns>The full data provider specific parameter name.</returns>
        protected string CreateCollectionParameterName(string baseParamName)
        {
            return "@" + baseParamName;
        }

        /// <summary>
        /// Gets <see cref="System.Data.IDbConnection"/> associated with this object.
        /// </summary>
        /// <value>A reference to the <see cref="System.Data.IDbConnection"/> object.</value>
        public IDbConnection Connection
        {
            get { return _connection; }
        }

        /// <summary>
        /// Gets an object that represents the <c>Action</c> table.
        /// </summary>
        /// <value>A reference to the <see cref="ActionCollection"/> object.</value>


        /// <summary>
        /// Begins a new database transaction.
        /// </summary>
        /// <seealso cref="CommitTransaction"/>
        /// <seealso cref="RollbackTransaction"/>
        /// <returns>An object representing the new transaction.</returns>
        public IDbTransaction BeginTransaction()
        {
            CheckTransactionState(false);
            _transaction = _connection.BeginTransaction();
            return _transaction;
        }

        /// <summary>
        /// Begins a new database transaction with the specified 
        /// transaction isolation level.
        /// <seealso cref="CommitTransaction"/>
        /// <seealso cref="RollbackTransaction"/>
        /// </summary>
        /// <param name="isolationLevel">The transaction isolation level.</param>
        /// <returns>An object representing the new transaction.</returns>
        public IDbTransaction BeginTransaction(IsolationLevel isolationLevel)
        {
            CheckTransactionState(false);
            _transaction = _connection.BeginTransaction(isolationLevel);
            return _transaction;
        }

        /// <summary>
        /// Commits the current database transaction.
        /// <seealso cref="BeginTransaction"/>
        /// <seealso cref="RollbackTransaction"/>
        /// </summary>
        public void CommitTransaction()
        {
            CheckTransactionState(true);
            _transaction.Commit();
            _transaction = null;
        }

        /// <summary>
        /// Rolls back the current transaction from a pending state.
        /// <seealso cref="BeginTransaction"/>
        /// <seealso cref="CommitTransaction"/>
        /// </summary>
        public void RollbackTransaction()
        {
            CheckTransactionState(true);
            _transaction.Rollback();
            _transaction = null;
        }

        // Checks the state of the current transaction
        private void CheckTransactionState(bool mustBeOpen)
        {
            if (mustBeOpen)
            {
                if (null == _transaction)
                    throw new InvalidOperationException("Transaction is not open.");
            }
            else
            {
                if (null != _transaction)
                    throw new InvalidOperationException("Transaction is already open.");
            }
        }

        /// <summary>
        /// Creates and returns a new <see cref="System.Data.IDbCommand"/> object.
        /// </summary>
        /// <param name="sqlText">The text of the query.</param>
        /// <returns>An <see cref="System.Data.IDbCommand"/> object.</returns>
        internal IDbCommand CreateCommand(string sqlText)
        {
            return CreateCommand(sqlText, false);
        }

        /// <summary>
        /// Creates and returns a new <see cref="System.Data.IDbCommand"/> object.
        /// </summary>
        /// <param name="sqlText">The text of the query.</param>
        /// <param name="procedure">Specifies whether the sqlText parameter is 
        /// the name of a stored procedure.</param>
        /// <returns>An <see cref="System.Data.IDbCommand"/> object.</returns>
        internal IDbCommand CreateCommand(string sqlText, bool procedure)
        {
            IDbCommand cmd = _connection.CreateCommand();
            cmd.CommandText = sqlText;
            cmd.Transaction = _transaction;
            if (procedure)
                cmd.CommandType = CommandType.StoredProcedure;
            return cmd;
        }

        /// <summary>
        /// Rolls back any pending transactions and closes the DB connection.
        /// An application can call the <c>Close</c> method more than
        /// one time without generating an exception.
        /// </summary>
        public virtual void Close()
        {
            if (null != _connection)
                _connection.Close();
        }

        /// <summary>
        /// Rolls back any pending transactions and closes the DB connection.
        /// </summary>
        public virtual void Dispose()
        {
            Close();
            if (null != _connection)
                _connection.Dispose();
        }
    } // End of MainDB_Base class
}
